/**
 * 副将表重新设计迁移脚本
 * 将副将表简化为只包含核心字段
 */

const mysql = require('mysql2/promise');
require('dotenv').config();

// 数据库配置 - 使用远程数据库
const DB_CONFIG = {
    host: process.env.DB_HOST || '43.143.253.188',
    port: parseInt(process.env.DB_PORT || '3306', 10),
    user: process.env.DB_USER || 'nextjs',
    password: process.env.DB_PASS || 'yEAiRE2jcxTyRBy5',
    database: process.env.DB_NAME || 'nextjs',
    charset: 'utf8mb4',
    timezone: '+08:00'
};

class DeputyGeneralTableMigration {
    constructor() {
        this.connection = null;
    }

    async connect() {
        try {
            this.connection = await mysql.createConnection(DB_CONFIG);
            console.log('✅ 数据库连接成功');
        } catch (error) {
            console.error('❌ 数据库连接失败:', error.message);
            throw error;
        }
    }

    async disconnect() {
        if (this.connection) {
            await this.connection.end();
            console.log('🔌 数据库连接已关闭');
        }
    }

    async backupExistingData() {
        console.log('\n📦 步骤1: 备份现有副将数据...');

        try {
            // 检查表是否存在
            const [tables] = await this.connection.execute(
                "SHOW TABLES LIKE 'deputy_general'"
            );

            if (tables.length === 0) {
                console.log('⚠️ 副将表不存在，跳过备份');
                return null;
            }

            // 备份现有数据
            const [rows] = await this.connection.execute(
                "SELECT * FROM deputy_general"
            );

            console.log(`📊 备份了 ${rows.length} 条副将数据`);

            // 保存到文件
            const fs = require('fs');
            const backupData = {
                timestamp: new Date().toISOString(),
                count: rows.length,
                data: rows
            };

            fs.writeFileSync(
                `deputy_general_backup_${Date.now()}.json`,
                JSON.stringify(backupData, null, 2)
            );

            console.log('✅ 数据备份完成');
            return rows;
        } catch (error) {
            console.error('❌ 数据备份失败:', error.message);
            throw error;
        }
    }

    async dropExistingTable() {
        console.log('\n🗑️ 步骤2: 删除现有副将表...');

        try {
            await this.connection.execute("DROP TABLE IF EXISTS deputy_general");
            console.log('✅ 现有副将表已删除');
        } catch (error) {
            console.error('❌ 删除表失败:', error.message);
            throw error;
        }
    }

    async createNewTable() {
        console.log('\n🏗️ 步骤3: 创建新的副将表...');

        const createTableSQL = `
            CREATE TABLE \`deputy_general\` (
              \`id\` int NOT NULL AUTO_INCREMENT COMMENT '主键ID',
              \`base_id\` int NOT NULL COMMENT '基础表id',
              \`czl\` decimal(4,2) NOT NULL DEFAULT '0.00' COMMENT '成长率',
              \`sp\` int NOT NULL DEFAULT '0' COMMENT '速度',
              \`ap\` int NOT NULL DEFAULT '0' COMMENT '攻击力',
              \`hp\` int NOT NULL DEFAULT '0' COMMENT '血量',
              \`mp\` int NOT NULL DEFAULT '0' COMMENT '精力',
              \`sp_points\` int NOT NULL DEFAULT '0' COMMENT '加点速点数',
              \`ap_points\` int NOT NULL DEFAULT '0' COMMENT '加点攻点数',
              \`hp_points\` int NOT NULL DEFAULT '0' COMMENT '加点血点数',
              \`mp_points\` int NOT NULL DEFAULT '0' COMMENT '加点精点数',
              \`loyalty\` int NOT NULL DEFAULT '100' COMMENT '忠诚度(0-100)',
              \`compatibility\` int NOT NULL DEFAULT '0' COMMENT '默契度(0-20万)',
              \`is_real\` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否为真',
              \`trans_count\` int NOT NULL DEFAULT '0' COMMENT '转职次数(0-4)',
              \`ultimate_skill_1_id\` int NULL COMMENT '无双1id',
              \`ultimate_skill_1_level\` int NOT NULL DEFAULT '0' COMMENT '无双1等级',
              \`ultimate_skill_2_id\` int NULL COMMENT '无双2id',
              \`ultimate_skill_2_level\` int NOT NULL DEFAULT '0' COMMENT '无双2等级',
              \`created_at\` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
              \`updated_at\` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
              PRIMARY KEY (\`id\`),
              KEY \`idx_base_id\` (\`base_id\`),
              KEY \`idx_loyalty\` (\`loyalty\`),
              KEY \`idx_compatibility\` (\`compatibility\`),
              KEY \`idx_trans_count\` (\`trans_count\`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='副将表';
        `;

        try {
            await this.connection.execute(createTableSQL);
            console.log('✅ 新的副将表创建成功');
        } catch (error) {
            console.error('❌ 创建表失败:', error.message);
            throw error;
        }
    }

    async migrateData(backupData) {
        if (!backupData || backupData.length === 0) {
            console.log('\n⚠️ 没有数据需要迁移');
            return;
        }

        console.log('\n🔄 步骤4: 迁移数据到新表...');

        let migratedCount = 0;
        let skippedCount = 0;

        for (const row of backupData) {
            try {
                // 映射旧字段到新字段
                const newRow = {
                    id: row.id,
                    base_id: row.base_id,
                    czl: row.czl || 0,
                    sp: row.sp || 0,
                    ap: row.ap || 0,
                    hp: row.hp || row.max_hp || 0,
                    mp: row.mp || row.max_mp || 0,
                    sp_points: 0, // 新字段，默认值
                    ap_points: 0, // 新字段，默认值
                    hp_points: 0, // 新字段，默认值
                    mp_points: 0, // 新字段，默认值
                    loyalty: row.zcd || row.loyalty || 100,
                    compatibility: row.mqd || row.compatibility || 0,
                    is_real: true, // 新字段，默认值
                    trans_count: row.trans || row.trans_count || 0,
                    ultimate_skill_1_id: null, // 新字段，默认值
                    ultimate_skill_1_level: 0, // 新字段，默认值
                    ultimate_skill_2_id: null, // 新字段，默认值
                    ultimate_skill_2_level: 0, // 新字段，默认值
                    created_at: row.created_at,
                    updated_at: row.updated_at
                };

                await this.connection.execute(
                    `INSERT INTO deputy_general (
                        id, base_id, czl, sp, ap, hp, mp, sp_points, ap_points, 
                        hp_points, mp_points, loyalty, compatibility, is_real, 
                        trans_count, ultimate_skill_1_id, ultimate_skill_1_level, 
                        ultimate_skill_2_id, ultimate_skill_2_level, created_at, updated_at
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
                    [
                        newRow.id, newRow.base_id, newRow.czl, newRow.sp, newRow.ap,
                        newRow.hp, newRow.mp, newRow.sp_points, newRow.ap_points,
                        newRow.hp_points, newRow.mp_points, newRow.loyalty,
                        newRow.compatibility, newRow.is_real, newRow.trans_count,
                        newRow.ultimate_skill_1_id, newRow.ultimate_skill_1_level,
                        newRow.ultimate_skill_2_id, newRow.ultimate_skill_2_level,
                        newRow.created_at, newRow.updated_at
                    ]
                );

                migratedCount++;
            } catch (error) {
                console.error(`❌ 迁移数据失败 (ID: ${row.id}):`, error.message);
                skippedCount++;
            }
        }

        console.log(`✅ 数据迁移完成: 成功 ${migratedCount} 条, 跳过 ${skippedCount} 条`);
    }

    async verifyMigration() {
        console.log('\n🔍 步骤5: 验证迁移结果...');

        try {
            const [rows] = await this.connection.execute("SELECT COUNT(*) as count FROM deputy_general");
            const [structure] = await this.connection.execute("DESCRIBE deputy_general");

            console.log(`📊 新表记录数: ${rows[0].count}`);
            console.log(`📋 新表字段数: ${structure.length}`);

            console.log('\n📋 新表字段列表:');
            structure.forEach(field => {
                console.log(`  - ${field.Field}: ${field.Type} ${field.Null === 'YES' ? '(可空)' : '(非空)'}`);
            });

            console.log('✅ 迁移验证完成');
        } catch (error) {
            console.error('❌ 验证失败:', error.message);
            throw error;
        }
    }

    async runMigration() {
        console.log('🚀 开始副将表重新设计迁移...\n');

        try {
            await this.connect();

            const backupData = await this.backupExistingData();
            await this.dropExistingTable();
            await this.createNewTable();
            await this.migrateData(backupData);
            await this.verifyMigration();

            console.log('\n🎉 副将表重新设计迁移完成！');
            console.log('\n📝 新表特点:');
            console.log('  - 只包含核心字段，简化了表结构');
            console.log('  - 移除了复杂的百分比字段和状态字段');
            console.log('  - 新增了加点系统字段');
            console.log('  - 新增了无双技能字段');
            console.log('  - 优化了索引结构');

        } catch (error) {
            console.error('\n💥 迁移过程中发生错误:', error.message);
            throw error;
        } finally {
            await this.disconnect();
        }
    }
}

async function main() {
    const migration = new DeputyGeneralTableMigration();
    await migration.runMigration();
}

if (require.main === module) {
    main().catch((error) => {
        console.error('💥 迁移脚本执行失败:', error.message);
        process.exit(1);
    });
}

module.exports = DeputyGeneralTableMigration;
